package lastfm;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import musictrackrecommendation.DatabaseDataset;
import musictrackrecommendation.ExperimentParameters;
import musictrackrecommendation.Utils;

import randomwalk.PreferencesVector;
import simulation.CumulativeDistributionFunction;

/**
 * @author  user
 */
public class LastfmDataset extends DatabaseDataset{

	public static final String SQLSERVER_JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public static final String SQLSERVER_JDBC_CONNECTION_URL = "jdbc:sqlserver://YOUR-5F64BCCFD1";
    public static final String SQLSERVER_USER = "recommendation";
    public static final String SQLSERVER_PASSWORD = "recommendation";
    public static final String USER_TABLE_NAME = "lastfm.dbo.small_user";
    public static final String TRACK_TABLE_NAME = "lastfm.dbo.small_track";
    public static final String TAG_TABLE_NAME = "lastfm.dbo.small_tag";
    public static final String USER_USER_TABLE_NAME = "lastfm.dbo.small_user_user_train_cosine";
    public static final String TRACK_TRACK_TABLE_NAME = "lastfm.dbo.small_track_track_train_cosine";
    public static final String USER_TRACK_TABLE_NAME = "lastfm.dbo.small_user_track_train";
    public static final String TRAIN_TABLE_NAME = "lastfm.dbo.small_user_track_train";
    public static final String QUIZ_TABLE_NAME = "lastfm.dbo.small_user_track_quiz";
    public static final String USER_TAG_TABLE_NAME = "lastfm.dbo.small_user_tag";
    public static final String TAG_TRACK_TABLE_NAME = "lastfm.dbo.small_tag_track";
    public static final String FRIENDS_TABLE_NAME = "lastfm.dbo.small_friends";
    public static final String USER_SIMILARITY_TABLE_NAME = "lastfm.dbo.small_user_similarity";
    //name of some attributes in tables
    public static final String ID_ATTRIBUTE = "id";

    @Override
    public int getUsersCount() {
        return getItemsCount(USER_TABLE_NAME);
    }

    public int getTracksCount() {
        return getItemsCount(TRACK_TABLE_NAME);
    }

    public int getTagsCount() {
        return getItemsCount(TAG_TABLE_NAME);
    }

    @Override
    public int getUserUserCount() {
        return getItemsCount(USER_USER_TABLE_NAME);
    }

    public int getUserTagCount() {
        return getItemsCount(USER_TAG_TABLE_NAME);
    }

    @Override
    public int getUserTrackCount() {
        return getItemsCount(USER_TRACK_TABLE_NAME);
    }

    public int getTagTrackCount() {
        return getItemsCount(TAG_TRACK_TABLE_NAME);
    }
    
    public int getTrackTrackCount() {
        return getItemsCount(TRACK_TRACK_TABLE_NAME);
    }

    /**
     *
     * @return # Users + # Tracks + # Tags
     */
    public int getPreferenceVectorLength() {
        int preferenceVectorLength = getUsersCount()
                + getTracksCount()
                + getTagsCount();
        return preferenceVectorLength;
    }

    @Override
    public int getUsersMaximalId() {
        return getMaximalId(USER_TABLE_NAME, ID_ATTRIBUTE);
    }

    @Override
    public int getTracksMaximalId() {
        return getMaximalId(TRACK_TABLE_NAME, ID_ATTRIBUTE);
    }

    public int getTagsMaximalId() {
        return getMaximalId(TAG_TABLE_NAME, ID_ATTRIBUTE);
    }

    /**
     *
     * @return size of adjacency matrix of random walk graph
     */
    public int getMatrixSize() {
        int matrixSize =
                getTagsMaximalId() + getTracksMaximalId() + getUsersMaximalId();
        return matrixSize;
    }
    
    public int [] getUsersAveragePlaycounts() {
    	int[] averagePlayCounts = new int[getUsersCount()];
 
    	try {
        	Class.forName(getJdbcDriverName());

            Connection con =
                    DriverManager.getConnection(
                    		getJdbcConnectionUrl(),
                    		getDatabaseUser(), 
                    		getPassword());

            Statement stmt = con.createStatement();
            String query = 
            	"SELECT user_id, avg( playcount ) FROM " + USER_TRACK_TABLE_NAME + " GROUP BY user_id ORDER BY user_id";
            ResultSet rs = stmt.executeQuery(query);

            int i = 0;
            
            while (rs.next()) {
            	averagePlayCounts[i] = rs.getInt(2);
                i++;
            }
            con.close();
            stmt.close();
        } 
        catch (ClassNotFoundException e) {
			System.out.println("Cannot register JDBC driver");
			System.out.println(e.getMessage());;
		} catch (SQLException e) {
			System.out.println("Cannot retrieve data from database");
			System.out.println(e.getMessage());;
		}
        return averagePlayCounts;
    }

    public int [] getTracksAveragePlaycounts() {
    	int[] averagePlayCounts = new int[getTracksMaximalId()+1];
 
    	try {
        	Class.forName(getJdbcDriverName());

            Connection con =
                    DriverManager.getConnection(
                    		getJdbcConnectionUrl(),
                    		getDatabaseUser(), 
                    		getPassword());

            Statement stmt = con.createStatement();
            String query = "SELECT track_id, avg( playcount ) FROM " + USER_TRACK_TABLE_NAME + " GROUP BY track_id";
            ResultSet rs = stmt.executeQuery(query);
            
            while (rs.next()) {
            	averagePlayCounts[rs.getInt(1)] = rs.getInt(2);
            }
            con.close();
            stmt.close();
        } 
        catch (ClassNotFoundException e) {
			System.out.println("Cannot register JDBC driver");
			System.out.println(e.getMessage());;
		} catch (SQLException e) {
			System.out.println("Cannot retrieve data from database");
			System.out.println(e.getMessage());;
		}
        return averagePlayCounts;
    }

    
    public void insertUsersSimilarity(int user1, int user2,
    		double similarity, String label) {
    	 try {
             Class.forName(getJdbcDriverName());
             Connection con = DriverManager.getConnection(getJdbcConnectionUrl(), 
             		getDatabaseUser(),
             		getPassword());

             PreparedStatement pstmt;
             
             pstmt = con.prepareStatement("insert into " + USER_SIMILARITY_TABLE_NAME + " values(?, ?, ?, ?)");
             pstmt.setInt(1, user1);
             pstmt.setInt(2, user2);
             pstmt.setDouble(3, similarity);
             pstmt.setString(4, label);
             pstmt.executeUpdate();
             
             //and user1 user2 reverse
             pstmt = con.prepareStatement("insert into " + USER_SIMILARITY_TABLE_NAME + " values(?, ?, ?, ?)");
             pstmt.setInt(1, user2);
             pstmt.setInt(2, user1);
             pstmt.setDouble(3, similarity);
             pstmt.setString(4, label);
             pstmt.executeUpdate();

             con.close();
         } 
         catch (Exception e) {
             System.out.println(e.getMessage());;
         }
    }
    
    @Override
    public PreferencesVector getRestartVector(int userId) {

        double[] userFriends = new double[getUsersMaximalId()+1];
        double[] userTracks = new double[getTracksMaximalId()+1];
        double[] userTags = new double[getTagsMaximalId()+1];

        try {
            Class.forName(getJdbcDriverName());
            Connection con = DriverManager.getConnection(getJdbcConnectionUrl(), 
            		getDatabaseUser(),
            		getPassword());

            PreparedStatement pstmt;
            ResultSet rs;

            //select all user friends
            pstmt = con.prepareStatement("select distinct " + LastfmUserUserSubmatrix.USER2_ID + ",similarity from " + USER_USER_TABLE_NAME + " where "+LastfmUserUserSubmatrix.USER1_ID +"= ?");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                userFriends[rs.getInt(1)] = rs.getDouble(2);
            }

            //select all user tracks
            pstmt = con.prepareStatement("select distinct track_id, playCount from " + USER_TRACK_TABLE_NAME + " where user_id = ?");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                userTracks[rs.getInt(1)] = (double) rs.getInt(2);
            }
            
            //select all user tags
            pstmt = con.prepareStatement("select distinct tag_id from " + USER_TAG_TABLE_NAME + " where user_id = ?");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                userTags[rs.getInt(1)] = 1.0;
            }
            rs.close();
            con.close();
        } 
        catch (Exception e) {
            System.out.println(e.getMessage());;
        }
        
        PreferencesVector restartVector = 
        		new PreferencesVector(userFriends, userTracks, userTags);
        
        return restartVector;
    }
    
    public LastfmUserUserSubmatrix getRestrictedUserUser(ArrayList<Integer> userIds) {
    	ArrayList<Integer> rowIds = new ArrayList<Integer>();
    	ArrayList<Integer> columnIds = new ArrayList<Integer>();
    	ArrayList<Double> values = new ArrayList<Double>();
        try {
        	Class.forName(getJdbcDriverName());

            Connection con =
                    DriverManager.getConnection(
                    		getJdbcConnectionUrl(),
                    		getDatabaseUser(), 
                    		getPassword());

            String usersString = Utils.getCommaSeparatedString(userIds);
            
            Statement stmt = con.createStatement();
            String query = "SELECT user1_id, user2_id, similarity FROM " 
            	+ USER_USER_TABLE_NAME 
            	+ " WHERE user1_id in " + usersString 
            	+ " AND user2_id in " + usersString;
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
            	rowIds.add(rs.getInt(1));
            	columnIds.add(rs.getInt(2));
            	values.add(rs.getDouble(3));
            }
            con.close();
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println(e.getMessage());;
        }
        int [] rowIdsArray = Utils.getIntArray(rowIds);
        int [] columnIdsArray = Utils.getIntArray(columnIds);
        double [] valuesArray = Utils.getDoubleArray(values);
    	return new LastfmUserUserSubmatrix(rowIdsArray, columnIdsArray, valuesArray, valuesArray);
    }
    
    public LastfmTrackTrackSubmatrix getRestrictedTrackTrack(ArrayList<Integer> trackIds) {
    	ArrayList<Integer> rowIds = new ArrayList<Integer>();
    	ArrayList<Integer> columnIds = new ArrayList<Integer>();
    	ArrayList<Double> values = new ArrayList<Double>();
        try {
        	Class.forName(getJdbcDriverName());

            Connection con =
                    DriverManager.getConnection(
                    		getJdbcConnectionUrl(),
                    		getDatabaseUser(), 
                    		getPassword());

            String trackString = Utils.getCommaSeparatedString(trackIds);
            
            Statement stmt = con.createStatement();
            String query = "SELECT track1_id, track2_id, similarity FROM " 
            	+ TRACK_TRACK_TABLE_NAME 
            	+ " WHERE track1_id in " + trackString 
            	+ " AND track2_id in " + trackString;
            	//+ " AND similarity > 0.0545";
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
            	rowIds.add(rs.getInt(1));
            	columnIds.add(rs.getInt(2));
            	values.add(rs.getDouble(3));
            }
            con.close();
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println(e.getMessage());;
        }
        int [] rowIdsArray = Utils.getIntArray(rowIds);
        int [] columnIdsArray = Utils.getIntArray(columnIds);
        double [] valuesArray = Utils.getDoubleArray(values);
    	return new LastfmTrackTrackSubmatrix(rowIdsArray, columnIdsArray, valuesArray, valuesArray);
    }
    
    public LastfmUserTrackSubmatrix getRestrictedUserTrack(ArrayList<Integer> userIds, ArrayList<Integer> trackIds) {
    	ArrayList<Integer> rowIds = new ArrayList<Integer>();
    	ArrayList<Integer> columnIds = new ArrayList<Integer>();
    	ArrayList<Double> values = new ArrayList<Double>();
        try {
        	Class.forName(getJdbcDriverName());

            Connection con =
                    DriverManager.getConnection(
                    		getJdbcConnectionUrl(),
                    		getDatabaseUser(), 
                    		getPassword());

            String usersString = Utils.getCommaSeparatedString(userIds);
            String tracksString = Utils.getCommaSeparatedString(trackIds);
            
            Statement stmt = con.createStatement();
            String query = "SELECT user_id, track_id, playcount FROM " 
            	+ USER_TRACK_TABLE_NAME 
            	+ " WHERE user_id in " + usersString 
            	+ " AND track_id in " + tracksString;
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
            	rowIds.add(rs.getInt(1));
            	columnIds.add(rs.getInt(2));
            	values.add(rs.getDouble(3));
            }
            con.close();
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println(e.getMessage());;
        }
        int [] rowIdsArray = Utils.getIntArray(rowIds);
        int [] columnIdsArray = Utils.getIntArray(columnIds);
        double [] valuesArray = Utils.getDoubleArray(values);
    	return new LastfmUserTrackSubmatrix(rowIdsArray, columnIdsArray, valuesArray, valuesArray);
    }

    @Override
	public String getDatabaseUser() {
		return SQLSERVER_USER;
	}

	@Override
	public String getJdbcConnectionUrl() {
		return SQLSERVER_JDBC_CONNECTION_URL;
	}

	@Override
	public String getJdbcDriverName() {
		return SQLSERVER_JDBC_DRIVER;
	}

	@Override
	public String getPassword() {
		return SQLSERVER_PASSWORD;
	}

	/**
	 * @return
	 */
	@Override
	public String getUserTrackTableName() {
		return USER_TRACK_TABLE_NAME;
	}

	/**
	 * @return
	 */
	@Override
	public String getUserUserTableName() {
		return USER_USER_TABLE_NAME;
	}

	/**
	 * @return
	 */
	@Override
	public String getUserTableName() {
		return USER_TABLE_NAME;
	}
	
	@Override
	public String getUserIdAttribute() {
		return ID_ATTRIBUTE;
	}
	
	/**
	 * @return
	 */
	public String getFriendsTableName() {
		return FRIENDS_TABLE_NAME;
	}

	/**
	 * @return
	 */
	@Override
	public String getTrackTrackTableName() {
		return TRACK_TRACK_TABLE_NAME;
	}

	@Override
	public String getUserTrackQuizName() {
		return QUIZ_TABLE_NAME;
	}

	@Override
	public String getUserTrackTrainName() {
		return TRAIN_TABLE_NAME;
	}

	@Override
	public CumulativeDistributionFunction getTrackSimilarItems(int trackId) {
		ArrayList<Integer> itemIds = new ArrayList<Integer>();
		ArrayList<Double> itemThresholds = new ArrayList<Double>();
		int firstTrackId = 0;
		
        try {
            Class.forName(getJdbcDriverName());
            Connection con = DriverManager.getConnection(getJdbcConnectionUrl(), 
            		getDatabaseUser(),
            		getPassword());

            PreparedStatement pstmt;
            ResultSet rs;

            ExperimentParameters parameters = ExperimentParameters.getInstance();
            
            pstmt = con.prepareStatement("select sum(playCount) from " + USER_TRACK_TABLE_NAME + " where track_id = ?");
            pstmt.setInt(1, trackId);
            rs = pstmt.executeQuery();
            double playCountSum = 1.0;
            if(rs.next())
            	playCountSum = rs.getDouble(1) / parameters.getTrackUserColumnNorm();

            //select all user tracks
            pstmt = con.prepareStatement("select distinct user_id, playCount from " + USER_TRACK_TABLE_NAME + " where track_id = ? ORDER BY user_id ASC");
            pstmt.setInt(1, trackId);
            rs = pstmt.executeQuery();

            double cumulation = 0.0;
            while (rs.next()) {
            	cumulation += rs.getDouble(2)/playCountSum;
            	itemIds.add(rs.getInt(1));
            	itemThresholds.add(cumulation);
            }
            firstTrackId = itemIds.size();
            
            pstmt = con.prepareStatement("select sum(similarity) from " + TRACK_TRACK_TABLE_NAME + " where "+LastfmTrackTrackSubmatrix.TRACK1_ID +"= ?");
            pstmt.setInt(1, trackId);
            rs = pstmt.executeQuery();
            double similaritySum = 1.0;
            if(rs.next())
            	similaritySum = rs.getDouble(1) / parameters.getTrackTrackColumnNorm();
            
            //select all similar users with normalized similarity
            pstmt = con.prepareStatement("select distinct " + LastfmTrackTrackSubmatrix.TRACK2_ID + ",similarity from " + TRACK_TRACK_TABLE_NAME + " where "+LastfmTrackTrackSubmatrix.TRACK1_ID +"= ? ORDER BY "  + LastfmTrackTrackSubmatrix.TRACK2_ID + " ASC");
            pstmt.setInt(1, trackId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
            	cumulation +=rs.getDouble(2)/similaritySum;
            	itemIds.add(rs.getInt(1));
            	itemThresholds.add(cumulation);
            }
            
            rs.close();
            con.close();
        } 
        catch (Exception e) {
        	System.out.println(e.getMessage());
        }
        
        return new CumulativeDistributionFunction (itemIds, itemThresholds, firstTrackId);
	}

	@Override
	public CumulativeDistributionFunction getUserSimilarItems(int userId) {
		ArrayList<Integer> itemIds = new ArrayList<Integer>();
		ArrayList<Double> itemThresholds = new ArrayList<Double>();
		int firstTrackId = 0;
		
        try {
            Class.forName(getJdbcDriverName());
            Connection con = DriverManager.getConnection(getJdbcConnectionUrl(), 
            		getDatabaseUser(),
            		getPassword());

            PreparedStatement pstmt;
            ResultSet rs;

            ExperimentParameters parameters = ExperimentParameters.getInstance();
            
            pstmt = con.prepareStatement("select sum(similarity) from " + USER_USER_TABLE_NAME + " where "+LastfmUserUserSubmatrix.USER1_ID +"= ?");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            double similaritySum = 1.0;
            if(rs.next())
            	similaritySum = rs.getDouble(1) / parameters.getUserUserColumnNorm();
            
            //select all similar users with normalized similarity
            pstmt = con.prepareStatement("select distinct " + LastfmUserUserSubmatrix.USER2_ID + ",similarity from " + USER_USER_TABLE_NAME + " where "+LastfmUserUserSubmatrix.USER1_ID +"= ? ORDER BY "  + LastfmUserUserSubmatrix.USER2_ID + " ASC");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            double cumulation = 0.0;
            while (rs.next()) {
            	cumulation += rs.getDouble(2)/similaritySum;
            	itemIds.add(rs.getInt(1));
            	itemThresholds.add(cumulation);
            }
            
            firstTrackId = itemIds.size();
            
            pstmt = con.prepareStatement("select sum(playCount) from " + USER_TRACK_TABLE_NAME + " where user_id = ?");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            double playCountSum = 1.0;
            if(rs.next())
            	playCountSum = rs.getDouble(1) / parameters.getUserTrackColumnNorm();

            //select all user tracks
            pstmt = con.prepareStatement("select distinct track_id, playCount from " + USER_TRACK_TABLE_NAME + " where user_id = ? ORDER BY track_id ASC");
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
            	cumulation +=rs.getDouble(2)/playCountSum;
            	itemIds.add(rs.getInt(1));
            	itemThresholds.add(cumulation);
            }
            

            rs.close();
            con.close();
            
        } 
        catch (Exception e) {
        	System.out.println(e.getMessage());
        }
        
        return new CumulativeDistributionFunction (itemIds, itemThresholds, firstTrackId);
	}
}
